package com.cn.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.Transaction;
import com.cn.bean.EnumState;
import com.cn.bean.Provider;
import com.cn.bean.Provider_servicecatalog;
import com.cn.bean.Provider_servicetype;
import com.cn.bean.Resident;
import com.cn.bean.Resident_collect;
import com.cn.bean.Servicecatalog;
import com.cn.bean.Servicetype;
import com.cn.dao.ProviderDAO;

import com.mysql.jdbc.Statement;

public class ProviderDAOImpl extends BaseHibernateDAOImpl implements ProviderDAO {

	/**
	 * 通过st_id查找provider
	 */
	public List<Object> findByST(int st_id) {
		try {
			getSession().beginTransaction();
			Servicetype servicetype = (Servicetype) getSession().get(Servicetype.class, st_id);
			Set<Provider_servicetype> pro_st = servicetype.getPro_st();
			List list = new ArrayList();
			for (Provider_servicetype i : pro_st) {
				Provider p = i.getProvider();
				list.add(p);
			}
			getSession().getTransaction().commit();
			return list;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	/**
	 * 通过sc_id查找provider
	 */
	public List<Object> findBySC(int sc_id, int cityID) {
		try {
			getSession().beginTransaction();
			Servicecatalog servicecatalog = (Servicecatalog) getSession().get(Servicecatalog.class, sc_id);
			Set<Provider_servicecatalog> pro_sc = servicecatalog.getPro_sc();
			List list = new ArrayList();
			for (Provider_servicecatalog i : pro_sc) {
				Provider p = i.getProvider();
				if (p.getState().equals(EnumState.STATE_PASSED) && p.getCity().getIndex() == cityID) {
					list.add(p);
				}
			}
			getSession().getTransaction().commit();
			return list;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	/**
	 * 通过用户名查找provider对象
	 * 
	 * @param username
	 * @return
	 */
	public Object findByUsername(String username) {
		try {
			String queryString = "from Provider as provider where username ='" + username + "'";
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(queryString);
			List list = queryObject.list();
			getSession().getTransaction().commit();
			if (list.size() == 0) {
				return null;
			}
			return list.get(0);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	/**
	 * 根据ID查找provider对象
	 * 
	 * @param id
	 * @return provider
	 */
	public Object findById(int id) {
		try {
			String queryString = "from Provider as provider where id =" + id;
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(queryString);
			List list = queryObject.list();
			getSession().getTransaction().commit();
			if (list.size() == 0) {
				return null;
			}
			return list.get(0);
		} catch (RuntimeException re) {
			throw re;
		}
	}

	/**
	 * 得到所有未认证供应商
	 * 
	 * @return 供应商列表
	 */
	public List listAllUNAUTHORIZED() {
		try {
			String hql = "from Provider as provider where state= '" + EnumState.STATE_UNAUTHORIZED + "'";
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(hql);
			List<Object> list = queryObject.list();
			getSession().getTransaction().commit();
			return list;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	/**
	 * 获取评分大于等于4的供应商为推荐供应商
	 * 
	 * @return 推荐的供应商列表
	 */
	public List listRecommendProviders() {
		try {
			String hql = "from Provider as provider where rank >=4 and state= '" + EnumState.STATE_PASSED
					+ "' order by rank DESC";		//从数据库provider表中找出等级大于等于4，状态为认证通过的供应商，并且将等级降序排列
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(hql);
			List<Object> list = queryObject.list();	//将查询出的结果用list的形式保存
			getSession().getTransaction().commit();	//提交事务
			return list;							//返回list
		} catch (RuntimeException re) {
			throw re;
		}
	}
/**
 * 根据pro_id寻找Provider_servicecatalog
 * @param pro_id
 * @return
 */
	public List getSCbyPid(int pro_id) {
		try {
			String hql = "from Provider_servicecatalog as prosc where prosc.provider.id= '" + pro_id + "'";
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(hql);
			List<Object> list = queryObject.list();
			getSession().getTransaction().commit();
			return list;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	// 服务商提交服务种类
	public boolean providerSaveCatalog(int pro_id, int sc_id) {

		String hql = "from Provider_servicecatalog as prosc where pro_id= '" + pro_id + "' and sc_id='" + sc_id + "'";
		getSession().beginTransaction();
		Query queryObject = getSession().createQuery(hql);
		List<Object> list = queryObject.list();
		getSession().getTransaction().commit();
		if (list.size() != 0)
			return false;

		System.out.println("pro_id=" + pro_id + "  sc_id=" + sc_id);
		Provider_servicecatalog psc = new Provider_servicecatalog();
		Provider p = new Provider();
		p.setId(pro_id);

		Servicecatalog sc = new Servicecatalog();
		sc.setId(sc_id);

		psc.setProvider(p);
		psc.setServicecatalog(sc);

		return save(psc);
	}

	// 服务商查看服务价格
	public List showServicePrice(int pro_id) {
		try {
			String queryString = "from Provider_servicecatalog as provider_servicecatalog where pro_id ='" + pro_id
					+ "'";
			getSession().beginTransaction();
			Query queryObject = getSession().createQuery(queryString);
			List<Object> list = queryObject.list();
			getSession().getTransaction().commit();
			if (list.size() == 0) {
				return null;
			}
			return list;
		} catch (RuntimeException re) {
			throw re;
		}
	}

	

public void providerRank() 
	{ 
		Transaction tx = null;
		 
		try {
			tx=getSession().beginTransaction();
			String queryString = "from Provider as providers ";
			Query queryObject = getSession().createQuery(queryString);
			List <Object>list = queryObject.list();
			if (list.size() == 0) {
				System.out.println("没有供应商");
				return;
			}
			for(int i=0;i<list.size();i++)
			{
				Provider provider=(Provider)list.get(i);
				int pro_id=provider.getId();//先得到每一个供应商的id
				//在所有订单中查找id为pro_id的订单并统计平均评分
				String queryString2 = "select AVG(rank) from Order as average where average.provider.id=" + pro_id;
				Query queryObject2 = getSession().createQuery(queryString2);
				List <Object>list2 = queryObject2.list();
				if(list2.get(0)==null){
					continue;
				}
				float newRank=Float.parseFloat(list2.get(0).toString());
				provider.setRank(newRank);
				getSession().update(provider);//更新供应商的评分
			}
			tx.commit();
			System.out.println("供应商评分已更新");
		} 
		catch (Exception re) {
			tx.rollback();
			re.printStackTrace();
		}
		}

	

	//列出所有被收藏的供应商
	public List<Object> findCollectedProvider(int re_id) {
		try {
			getSession().beginTransaction();
			Resident re = (Resident) getSession().get(Resident.class, re_id);
			Set<Resident_collect> pro_re = re.getPro_re();
			List list = new ArrayList();
			for (Resident_collect i : pro_re) {
				Provider p = i.getProvider();
					list.add(p);
			}
			getSession().getTransaction().commit();
			return list;
		} catch (RuntimeException re) {
			throw re;
		}
	
	}
	}

	



